package com.oasystem.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.oasystem.domain.Member;
import com.oasystem.domain.MemberSummary;
import com.oasystem.domain.PagingBean;
import com.oasystem.domain.SelledGoodsSearchResult;

public class MemberDAO {
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void insertMember(final Member member) {
		KeyHolder key = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				String sql = "insert into member (memberCardNumber,name,idCardNumber,phoneNumber,address,operatorID,goodsID,goodsPrice,profitReturnPercent,profitReturnAmount,departmentID,createDate,description) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
				PreparedStatement ps = connection.prepareStatement(sql);
				ps.setString(1, member.getMemberCardNumber());
				ps.setString(2, member.getName());
				ps.setString(3, member.getIdCardNumber());
				ps.setString(4, member.getPhoneNumber());
				ps.setString(5, member.getAddress());
				ps.setInt(6, member.getOperatorID());
				ps.setInt(7, member.getGoodsID());
				ps.setDouble(8, member.getGoodsPrice());
				ps.setDouble(9, member.getProfitReturnPercent());
				ps.setDouble(10, member.getProfitReturnAmount());
				ps.setInt(11, member.getDepartmentID());
				ps.setString(12, member.getCreateDateString());
				ps.setString(13, member.getDescription());
				return ps;
			}
		},key);
		member.setId(key.getKey().longValue());
	}

	public void updateMember(final Member member) {
		jdbcTemplate.update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				String sql = "update member set memberCardNumber=?,name=?,idCardNumber=?,phoneNumber=?,address=?,operatorID=?,goodsID=?,goodsPrice=?,profitReturnPercent=?,profitReturnAmount=?,departmentID=?,createDate=?,description=? where id=?";
				PreparedStatement ps = connection.prepareStatement(sql);
				ps.setString(1, member.getMemberCardNumber());
				ps.setString(2, member.getName());
				ps.setString(3, member.getIdCardNumber());
				ps.setString(4, member.getPhoneNumber());
				ps.setString(5, member.getAddress());
				ps.setInt(6, member.getOperatorID());
				ps.setInt(7, member.getGoodsID());
				ps.setDouble(8, member.getGoodsPrice());
				ps.setDouble(9, member.getProfitReturnPercent());
				ps.setDouble(10, member.getProfitReturnAmount());
				ps.setInt(11, member.getDepartmentID());
				ps.setString(12, member.getCreateDateString());
				ps.setString(13, member.getDescription());
				ps.setLong(14, member.getId());
				return ps;
			}});
	}

	public void getPagingMember(PagingBean<Member> pagingBean) {
		// TODO Auto-generated method stub

	}

	public List<Member> searchForList(Member member) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from member where 1=1 ");

		if (member.getMemberCardNumber() != null) {
			sb.append(" and memberCardNumber='" + member.getMemberCardNumber()
					+ "'");
		}
		if (member.getName() != null) {
			sb.append(" and name='" + member.getName() + "'");
		}
		if (member.getIdCardNumber() != null) {
			sb.append(" and idCardNumber='" + member.getIdCardNumber() + "'");
			;
		}
		if (member.getPhoneNumber() != null) {
			sb.append(" and phoneNumber='" + member.getPhoneNumber() + "'");
		}
		if (member.getAddress() != null) {
			sb.append(" and address like '%" + member.getAddress() + "%'");
		}
		if (member.getOperatorID() != 0 && member.getOperatorID() != -1) {
			sb.append(" and operatorID=" + member.getOperatorID());
		}
		if (member.getGoodsID() != 0 && member.getGoodsID() != -1) {
			sb.append(" and goodsID=" + member.getGoodsID());
		}
		if (member.getDepartmentID() != 0 && member.getDepartmentID() != -1) {
			sb.append(" and departmentID=" + member.getDepartmentID());
		}
		if (member.getGoodsPrice()!=null) {
			sb.append(" and goodsPrice=" + member.getGoodsPrice());
		}
		if (member.getProfitReturnPercent()!=null) {
			sb.append(" and profitReturnPercent="
					+ member.getProfitReturnPercent());
		}
		if (member.getProfitReturnAmount()!=null) {
			sb.append(" and profitReturnAmount="
					+ member.getProfitReturnAmount());
		}
		if (member.getDescription() != null) {
			sb.append(" and description like '%" + member.getDescription()
					+ "%'");
		}
		if (member.getCreateDateString() != null) {
			sb.append(" and createDate like '%" + member.getCreateDateString()
					+ "%'");
		}

		// String sql =
		// "select * from member where concat(memberCardNumber,name,idCardNumber,phoneNumber,address,operatorID,goodsID,departmentID,goodsPrice,profitReturnPercent,profitReturnAmount,description,createDate) like '%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'";
		return jdbcTemplate.query(sb.toString(), new RowMapper<Member>() {

			public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
				Member member = new Member();
				member.setAddress(rs.getString("address"));
				member.setCreateDateString(rs.getString("createDate").substring(0,10));
				member.setDepartmentID(rs.getInt("departmentID"));
				member.setDescription(rs.getString("description"));
				member.setGoodsID(rs.getInt("goodsID"));
				member.setGoodsPrice(rs.getDouble("goodsPrice"));
				member.setIdCardNumber(rs.getString("idCardNumber"));
				member.setMemberCardNumber(rs.getString("memberCardNumber"));
				member.setName(rs.getString("name"));
				member.setOperatorID(rs.getInt("operatorID"));
				member.setPhoneNumber(rs.getString("phoneNumber"));
				member.setProfitReturnAmount(rs.getDouble("profitReturnAmount"));
				member.setProfitReturnPercent(rs
						.getDouble("profitReturnPercent"));
				return member;
			}
		});
	}

	public void searchForPagingList(Member member, PagingBean<Member> pagingBean) {

		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();

		StringBuffer wherePhase = new StringBuffer(" where 1=1 ");

		if (!member.getMemberCardNumber().equals("")) {
			wherePhase.append(" and m.memberCardNumber like '%"
					+ member.getMemberCardNumber() + "%'");
		}
		if (!member.getName().equals("")) {
			wherePhase.append(" and m.name like '%" + member.getName() + "%'");
		}
		if (!member.getIdCardNumber().equals("")) {
			wherePhase.append(" and idCardNumber like '%"
					+ member.getIdCardNumber() + "%'");
		}
		if (!member.getPhoneNumber().equals("")) {
			wherePhase.append(" and phoneNumber like '%"
					+ member.getPhoneNumber() + "%'");
		}
		if (!member.getAddress().equals("")) {
			wherePhase.append(" and address like '%" + member.getAddress()
					+ "%'");
		}
		if (member.getOperatorID() != 0 && member.getOperatorID() != -1) {
			wherePhase.append(" and operatorID=" + member.getOperatorID());
		}
		if (member.getGoodsID() != 0 && member.getGoodsID() != -1) {
			wherePhase.append(" and goodsID=" + member.getGoodsID());
		}
		if (member.getDepartmentID() != 0 && member.getDepartmentID() != -1) {
			wherePhase.append(" and departmentID=" + member.getDepartmentID());
		}
		if (member.getGoodsPrice()!=null ) {
			wherePhase.append(" and m.goodsPrice=" + member.getGoodsPrice());
		}
		if (member.getProfitReturnPercent() != null) {
			wherePhase.append(" and profitReturnPercent="
					+ member.getProfitReturnPercent());
		}
		if (member.getProfitReturnAmount()!=null ) {
			wherePhase.append(" and profitReturnAmount="
					+ member.getProfitReturnAmount());
		}
		if (!member.getFromDate().equals("") && !member.getToDate().equals("")) {
			wherePhase.append(" and m.createDate between '"
					+ member.getFromDate()+" 00:00:00" + "' and '" + member.getToDate()+" 23:59:59"
					+ "' ");
		}

		pagingBean.setAllRecordCount(jdbcTemplate
				.queryForInt("select count(*) from member m"
						+ wherePhase.toString()));

		// String sql =
		// "select * from member where concat(memberCardNumber,name,idCardNumber,phoneNumber,address,operatorID,goodsID,departmentID,goodsPrice,profitReturnPercent,profitReturnAmount,description,createDate) like '%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'";
		final String sql = "select * from member m,operator o,goods g,department d "
				+ wherePhase.toString()
				+ " and m.operatorID=o.id and m.goodsID=g.id and m.departmentID=d.id  order by m.createDate desc limit ?,?";
		pagingBean.setData(jdbcTemplate.query(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setInt(1, currentRecordIndex);
				ps.setInt(2, pageSize);
				return ps;
			}
		}, new RowMapper<Member>() {

			public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
				Member member = new Member();
				member.setId(rs.getLong("m.id"));
				member.setAddress(rs.getString("m.address"));
				member.setCreateDateString(rs.getString("m.createDate").substring(0,10));
				member.setDepartmentID(rs.getInt("m.departmentID"));
				member.setDepartmentName(rs.getString("d.departmentName"));
				member.setDescription(rs.getString("m.description"));
				member.setGoodsID(rs.getInt("m.goodsID"));
				member.setGoodsName(rs.getString("g.name"));
				member.setGoodsPrice(rs.getDouble("m.goodsPrice"));
				member.setIdCardNumber(rs.getString("m.idCardNumber"));
				member.setMemberCardNumber(rs.getString("m.memberCardNumber"));
				member.setName(rs.getString("m.name"));
				member.setOperatorID(rs.getInt("m.operatorID"));
				member.setOperatorName(rs.getString("o.name"));
				member.setPhoneNumber(rs.getString("m.phoneNumber"));
				member.setProfitReturnAmount(rs
						.getDouble("m.profitReturnAmount"));
				member.setProfitReturnPercent(rs
						.getDouble("m.profitReturnPercent"));
				return member;
			}
		}));
	}


	public MemberSummary loadSummary(String from, String to) {
		MemberSummary memberSummary = new MemberSummary();
		
		String memberCount = "select count(id) from member where createDate between ? and ?";
		memberSummary.setMemberCount(String.valueOf(jdbcTemplate.queryForInt(memberCount, new Object[]{from,to}, new int[]{Types.TIMESTAMP,Types.TIMESTAMP})));
		
		String totalMemberCount = "select count(id) from member";
		memberSummary.setTotalMemberCount(String.valueOf(jdbcTemplate.queryForInt(totalMemberCount)));
		
		String cost = "select sum(goodsPrice) from member where createDate between ? and ?";
		memberSummary.setCost(String.valueOf(jdbcTemplate.queryForInt(cost, new Object[]{from,to}, new int[]{Types.TIMESTAMP,Types.TIMESTAMP})));
		
		String totalCost = "select sum(goodsPrice) from member";
		memberSummary.setTotalCost(String.valueOf(jdbcTemplate.queryForInt(totalCost)));
		
		String goodsCount = "select count(goodsID) from member where createDate between ? and ?";
		memberSummary.setSelledGoodsCount(String.valueOf(jdbcTemplate.queryForInt(goodsCount, new Object[]{from,to}, new int[]{Types.TIMESTAMP,Types.TIMESTAMP})));
		
		String totalGoodsCount = "select count(goodsID) from member";
		memberSummary.setTotalSelledGoodsCount(String.valueOf(jdbcTemplate.queryForInt(totalGoodsCount)));

		return memberSummary;
	}

	public SelledGoodsSearchResult loadSelledGoods(String from, String to) {
//		// TODO Auto-generated method stub
//
//		String sql = "select goods.name, count(goods.id) from member,goods where member.goodsID=goods.id and member.createDate between ? and ?";
//		SelledGoodsSearchResult selledGoodsSearchResult = new SelledGoodsSearchResult();
//		selledGoodsSearchResult.setGoodsName("");
//		selledGoodsSearchResult.setSelledGoodsCount("");
//		return selledGoodsSearchResult;
		return null;
	}

	public void retrieveMemberGoodsCostPagingList(final String from, final String to,
			PagingBean<Member> pagingBean) {
		final String sql = "select m.memberCardNumber,m.name,goodsPrice,g.name,m.createDate from member m,goods g where m.goodsID=g.id and m.createDate between ? and ? order by createDate desc limit ?,?";
		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();
		
		pagingBean.setAllRecordCount(jdbcTemplate
				.queryForInt("select count(*) from member where createDate between ? and ?",new Object[]{from,to},new int[]{Types.TIMESTAMP,Types.TIMESTAMP}));
		
		pagingBean.setData(jdbcTemplate.query(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setString(1, from);
				ps.setString(2, to);
				ps.setInt(3, currentRecordIndex);
				ps.setInt(4, pageSize);
				return ps;
			}
		}, new RowMapper<Member>() {

			public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
				Member member = new Member();
				member.setCreateDateString(rs.getString("m.createDate").substring(0,10));
				member.setGoodsName(rs.getString("g.name"));
				member.setGoodsPrice(rs.getDouble("m.goodsPrice"));
				member.setMemberCardNumber(rs.getString("m.memberCardNumber"));
				member.setName(rs.getString("m.name"));
				return member;
			}
		}));
	}

	public void retrieveGoodsStatisticsList(final String from, final String to,
			PagingBean<SelledGoodsSearchResult> pagingBean) {
		final String sql = "select g.name,count(goodsID) as goodsCount from member m,goods g where m.goodsID=g.id and m.createDate between ? and ?  group by goodsID limit ?,?";
		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();

		pagingBean.setAllRecordCount(jdbcTemplate
				.queryForInt("select count(distinct goodsID) from member"));

		pagingBean.setData(jdbcTemplate.query(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setString(1, from);
				ps.setString(2, to);
				ps.setInt(3, currentRecordIndex);
				ps.setInt(4, pageSize);
				System.out.println(from + " "+ to +" "+currentRecordIndex+" "+pageSize);
				return ps;
			}
		}, new RowMapper<SelledGoodsSearchResult>() {

			public SelledGoodsSearchResult mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				SelledGoodsSearchResult result = new SelledGoodsSearchResult();
				result.setGoodsName(rs.getString("g.name"));
				result.setSelledGoodsCount(rs.getString("goodsCount"));
				return result;
			}
		}));
	}

	public Member loadMember(Long memberID) {
		return jdbcTemplate.queryForObject("select * from member m,operator o,goods g,department d where m.operatorID=o.id and m.goodsID=g.id and m.departmentID=d.id and m.id=?", new Object[]{memberID}, new RowMapper<Member>() {

			public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
				Member member = new Member();
				member.setId(rs.getLong("m.id"));
				member.setAddress(rs.getString("m.address"));
				member.setCreateDateString(rs.getString("m.createDate").substring(0,10));
				member.setDepartmentID(rs.getInt("m.departmentID"));
				member.setDepartmentName(rs.getString("d.departmentName"));
				member.setDescription(rs.getString("m.description"));
				member.setGoodsID(rs.getInt("m.goodsID"));
				member.setGoodsName(rs.getString("g.name"));
				member.setGoodsPrice(rs.getDouble("m.goodsPrice"));
				member.setIdCardNumber(rs.getString("m.idCardNumber"));
				member.setMemberCardNumber(rs.getString("m.memberCardNumber"));
				member.setName(rs.getString("m.name"));
				member.setOperatorID(rs.getInt("m.operatorID"));
				member.setOperatorName(rs.getString("o.name"));
				member.setPhoneNumber(rs.getString("m.phoneNumber"));
				member.setProfitReturnAmount(rs
						.getDouble("m.profitReturnAmount"));
				member.setProfitReturnPercent(rs
						.getDouble("m.profitReturnPercent"));
				return member;
			}
		});
	}

	public void deleteMember(Long memberID) {
		jdbcTemplate.update("delete from member where id=?",memberID );
		jdbcTemplate.update("delete from memberprofitreturned where memberID=?",memberID );
		jdbcTemplate.update("delete from profitmemberrecord where memberID=?",memberID );
	}

	public void deleteOldMemberData(String dateTime) {
//		jdbcTemplate.update("insert into oldmemberinfo select * from member where createDate<?",new Object[]{dateTime},new int[]{Types.TIMESTAMP});
//		jdbcTemplate.update("delete mpr from  member m,memberprofitreturned mpr where m.id=mpr.memberID and m.createDate<?",new Object[]{dateTime},new int[]{Types.TIMESTAMP});
//		jdbcTemplate.update("delete pm from member m,profitmemberrecord pm where pm.memberID=m.id and m.createDate<? and pm.remainAmount=0",new Object[]{dateTime},new int[]{Types.TIMESTAMP});
		dateTime+=" 23:59:59";
		List<String> idList = jdbcTemplate.query("select p.memberID from profitmemberrecord p,member m where p.memberID=m.id and p.remainAmount=0 and m.createDate<?",new Object[]{dateTime},new int[]{Types.TIMESTAMP},new RowMapper<String>() {

			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString("p.memberID");
			}
		});
		StringBuilder sb = new StringBuilder();
		int size=idList.size();
		for(int i=0;i<size;i++){
			sb.append(idList.get(i));
			if((i+1)<size){
				sb.append(",");
			}
		}
		if(size!=0){
		jdbcTemplate.update("insert into oldmemberinfo select * from member where id in("+sb.toString()+")");
		jdbcTemplate.update("delete from member where id in("+sb.toString()+")");
		jdbcTemplate.update("delete from memberprofitreturned where memberID in("+sb.toString()+")");
		jdbcTemplate.update("delete from profitmemberrecord where memberID in("+sb.toString()+")");
		}
	}

}
